package com.stud.system.factory;

import com.stud.common.utils.Toolkit;

/**
 * @author Stud
 * @title XXXXX
 * @desription XXXXX
 * @data 2022-09-12 16:40
 */
public class AreaSqlFactory {

    /**
     * 向上递归查询上级行政区域
     *
     * @param areaIds
     * @return
     */
    public static String recurseUp(Long... areaIds) {
        StringBuilder sb = new StringBuilder();
        sb.append(" with recursive area_cte as ( ");
        sb.append("     select id, area_name, parent_id, 0 as level ");
        sb.append("     from t_area ");
        sb.append("     where id in (" + Toolkit.Obj2In(areaIds) + ") ");
        sb.append("     union all ");
        sb.append("     select t.id, t.area_name, t.parent_id, level + 1 from t_area t ");
        sb.append("     inner join area_cte cte on t.id = cte.parent_id ");
        sb.append(" ) ");
        sb.append(" select id, area_name, parent_id,min(level) as level,count(id) as num from area_cte ");
        sb.append(" group by id, area_name, parent_id ");
        return sb.toString();
    }

    /**
     * 向上递归查询上级行政区域
     *
     * @param areaId
     * @return
     */
    public static String recurseUpName(Long areaId) {
        StringBuilder sb = new StringBuilder();
        sb.append(" with recursive area_cte as ( ");
        sb.append("     select id, area_name, parent_id ");
        sb.append("     from t_area ");
        sb.append("     where id = " + areaId);
        sb.append("     union all ");
        sb.append("     select t.id, t.area_name, t.parent_id from t_area t ");
        sb.append("     inner join area_cte cte on t.id = cte.parent_id ");
        sb.append(" ) ");
        sb.append(" select area_name from area_cte ");
        return sb.toString();
    }

    /**
     * 递归查询下级行政区域
     *
     * @param areaId
     * @return
     */
    public static String recurseDownNoSelect(Long areaId) {
        StringBuilder sb = new StringBuilder();
        sb.append(" with recursive area_cte as ( ");
        sb.append("     select id, area_name, area_no, parent_id, 0 as level ");
        sb.append("     from t_area where id = " + areaId);
        sb.append("     union all ");
        sb.append("     select t.id, t.area_name, t.area_no, t.parent_id, level + 1 from t_area t ");
        sb.append("     inner join area_cte cte on t.parent_id = cte.id ");
        sb.append(" ) ");
        return sb.toString();
    }

    /**
     * 递归查询下级行政区域
     *
     * @param areaId
     * @return
     */
    public static String recurseDown(Long areaId) {
        StringBuilder sb = new StringBuilder();
        sb.append(" with recursive area_cte as ( ");
        sb.append("     select id, area_name, area_no, parent_id, 0 as level ");
        sb.append("     from t_area where id = " + areaId);
        sb.append("     union all ");
        sb.append("     select t.id, t.area_name, t.area_no, t.parent_id, level + 1 from t_area t ");
        sb.append("     inner join area_cte cte on t.parent_id = cte.id ");
        sb.append(" ) ");
        sb.append(" select id, area_name, area_no, parent_id, level from area_cte ");
        return sb.toString();
    }

    /**
     * 递归查询下级行政区域ID
     *
     * @param areaId
     * @return
     */
    public static String recurseDownForId(Long areaId) {
        StringBuilder sb = new StringBuilder();
        sb.append(" with recursive area_cte as ( ");
        sb.append("     select id, area_name, area_no, parent_id, 0 as level ");
        sb.append("     from t_area where id = " + areaId);
        sb.append("     union all ");
        sb.append("     select t.id, t.area_name, t.area_no, t.parent_id, level + 1 from t_area t ");
        sb.append("     inner join area_cte cte on t.parent_id = cte.id ");
        sb.append(" ) ");
        sb.append(" select id from area_cte ");
        return sb.toString();
    }

    /**
     * 超级用户查询绑定的项目区域
     *
     * @return
     */
    public static String projectArea1() {
        StringBuilder sb = new StringBuilder();
        sb.append(" select distinct area_id from t_project ");
        sb.append(" where del_flag = 0 and area_id is not null ");
        return sb.toString();
    }

    /**
     * 普通用户查询绑定的项目区域
     *
     * @return
     */
    public static String projectArea2() {
        StringBuilder sb = new StringBuilder();
        sb.append(" select distinct t2.area_id from t_user_project_link t1 ");
        sb.append(" inner join t_project t2 on t1.project_id = t2.id ");
        sb.append(" where t2.del_flag = 0 and t2.area_id is not null ");
        sb.append(" where t1.user_id = ? ");
        return sb.toString();
    }

    /**
     * 超级用户查询设备已绑定项目的区域
     *
     * @return
     */
    public static String vehicleArea1() {
        StringBuilder sb = new StringBuilder();
        sb.append(" select distinct t2.area_id from t_project t2 ");
        sb.append(" inner join t_vehicle t3 on t2.id = t3.project_id ");
        sb.append(" where t2.del_flag = 0 and t2.area_id is not null and t3.del_flag = 0 ");
        return sb.toString();
    }

    /**
     * 普通用户查询设备已绑定项目的区域
     *
     * @return
     */
    public static String vehicleArea2() {
        StringBuilder sb = new StringBuilder();
        sb.append(" select distinct t2.area_id from t_user_project_link t1 ");
        sb.append(" inner join t_project t2 on t1.project_id = t2.id ");
        sb.append(" inner join t_vehicle t3 on t2.id = t3.project_id ");
        sb.append(" where t1.user_id = ? and t2.del_flag = 0 and t2.area_id is not null and t3.del_flag = 0 ");
        return sb.toString();
    }

}
